Imports DAL
Imports MyClasses
Imports System.Data.SqlClient
Imports System.Configuration

Public Class DPurchaseItemList
    'Public Shared Function GetItemList(ByVal Vendor_ID As Integer, ByVal ReOrder As String) As DataTable
    '    Dim StrSql As String
    '    Dim strWhere As String = ""
    '    If ReOrder = "" Then ReOrder = 0
    '    Try

    '        If Vendor_ID <> 0 Then strWhere = "AND tstock.VendorID=" & Vendor_ID

    '        StrSql = "SELECT  mPitem.ItemName, case when OpeningStock IS NULL then 0 else OpeningStock end AS CurrentStock, 0 AS  NearToExpiredItem, mPitem.MaxReorderqty, 'FALSE' AS Reorder" & _
    '                 " FROM mPItem" & _
    '                 " WHERE MpItem.ItemID NOT IN (SELECT ItemID FROM tStock)" & _
    '                 " UNION" & _
    '                 " SELECT mPitem.ItemName, " & _
    '                 " (SELECT sum(quantity) FROM tstock WHERE tstock.itemID = tstocksummary.ItemID " & strWhere & ") AS CurrentStock, " & _
    '                 " case when (SELECT sum(quantity) FROM tstock WHERE tstock.itemID = tstocksummary.ItemID AND DateDiff(Day,getdate(),tstock.ExpDate) < 60 " & strWhere & " ) IS NOT NULL then (SELECT sum(quantity) FROM tstock WHERE tstock.itemID = tstocksummary.ItemID AND DateDiff(Day,getdate(),tstock.ExpDate) < 60 " & strWhere & " ) else 0 end as NearToExpiredItem," & _
    '                 " mPitem.MaxReorderqty, 'FALSE' AS Reorder" & _
    '                 " FROM tstocksummary INNER JOIN  mPitem ON (tstocksummary.ItemID = mPitem.ItemID)" & _
    '                 " WHERE "

    '        If Convert.ToInt32(ReOrder) <> 0 Then
    '            StrSql = StrSql & " (( SELECT sum(quantity) from tstock WHERE tstock.itemID = tstocksummary.ItemID " & strWhere & ") <= (mPitem.minReorderLevel + (mPitem.minReorderLevel * " & ReOrder & " /100)))"
    '        Else
    '            StrSql = StrSql & "  (( SELECT sum(quantity) from tstock WHERE tstock.itemID = tstocksummary.ItemID " & strWhere & ") <= mPitem.minReorderLevel)"
    '        End If
    '        StrSql = StrSql & " AND (SELECT sum(quantity) from tstock WHERE tstock.itemID = tstocksummary.ItemID " & strWhere & ") IS NOT  NULL "

    '        Return DbHelper.ExecuteDataTable(StrSql)

    '    Catch ex As Exception
    '        DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchaseItemList", "GetItemList")
    '        Return Nothing
    '    End Try

    'End Function

    Public Shared Function GetItemList(ByVal Vendor_ID As Integer, ByVal ReOrder As String) As DataTable
        Dim StrSql As String
        Dim strWhere As String = ""
        If ReOrder = "" Then ReOrder = 0
        Try

            If Vendor_ID <> 0 Then strWhere = " AND  I.ItemID IN(SELECT R.ItemID FROM tRelation R WHERE R.VendorID=" & Vendor_ID & ")"

            StrSql = "SELECT I.ItemName, S.CurrentStock," & _
                    " ISNULL((SELECT SUM(Quantity) FROM tstock WHERE tStock.ItemID = S.ItemID AND DATEDIFF(DAY,GETDATE(),tStock.ExpDate) BETWEEN 0 AND " & ConfigurationManager.AppSettings("NearToExpiry") & " AND tStock.VendorID=" & Vendor_ID & " ) ,0) AS NearToExpiredItem," & _
                    " I.MinReorderLevel, 'FALSE' AS Reorder " & _
                    " FROM tStockSummary S INNER JOIN  mpItem  I ON (S.ItemID = I.ItemID)" & _
                    " WHERE  S.CurrentStock <= case when I.minReorderLevel = 0 then (I.MinReorderLevel + (1 * " & ReOrder & "  /100)) else (I.MinReorderLevel + ( I.MinReorderLevel * " & ReOrder & "  /100))end"

            'If Convert.ToInt32(ReOrder) <> 0 Then
            '    StrSql = StrSql & " (( SELECT sum(quantity) from tstock WHERE tstock.itemID = tstocksummary.ItemID " & strWhere & ") <= (mPitem.minReorderLevel + (mPitem.minReorderLevel * " & ReOrder & " /100)))"
            'Else
            '    StrSql = StrSql & "  (( SELECT sum(quantity) from tstock WHERE tstock.itemID = tstocksummary.ItemID " & strWhere & ") <= mPitem.minReorderLevel)"
            'End If
            'StrSql = StrSql & " AND (SELECT sum(quantity) from tstock WHERE tstock.itemID = tstocksummary.ItemID " & strWhere & ") IS NOT  NULL "

            Return DbHelper.ExecuteDataTable(StrSql & strWhere)

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DPurchaseItemList", "GetItemList")
            Return Nothing
        End Try

    End Function
End Class
